﻿//  --------------------------------
//  Copyright (c) AW Systems, Inc. All rights reserved.
//  This source code is made available under the terms of the Microsoft Public License (Ms-PL)
//  http://www.codeplex.com/SLAM/license
//  ---------------------------------

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Linq;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using AWS.SLAM.Configuration;
using AWS.SLAM.Logging;

namespace AWS.SLAM.Data
{
    internal class SqlDataRepository : IDisposable
    {
        #region Private State

        private DataContext _dataContext;
        private static List<string> _verifiedTableList = new List<string>();
        private static List<string> _updatedTableList = new List<string>();
        private static bool _databaseCreated;

        #endregion

        #region Constructors and Destructors

        public SqlDataRepository(string connectionString)
        {
            _dataContext = new DataContext(connectionString); 
        }

        ~SqlDataRepository()
        {
            _dataContext.Dispose();
        }

        #endregion

        #region Database Creation

        public void ClearTable(string tableSchema, string tableName)
        {   
            _dataContext.ExecuteCommand(String.Format(Queries.ClearTable, tableSchema, tableName));
        }

        public void CreateAssociationTable(string tableSchema, string tableName, string firstType, string secondType)
        {
            if (firstType == secondType)
            {
                _dataContext.ExecuteCommand(String.Format(Queries.CreateAssociationTable, tableSchema, tableName, firstType, secondType + "2"));
            }
            else
            {
                _dataContext.ExecuteCommand(String.Format(Queries.CreateAssociationTable, tableSchema, tableName, firstType, secondType));
            }
        }

        public void CreateContentTable(string tableSchema, string tableName, IEnumerable<Field> fields)
        {
            string columnList = "";
            foreach (Field field in fields)
            {
                if (field is SqlMappedField)
                {
                    SqlMappedField sqlField = (SqlMappedField)field;
                    columnList += String.Format("[{0}] [{1} {2}, ", GetColumnName(sqlField), sqlField.SqlType.Contains("(") ? sqlField.SqlType.Replace("(", "](") : sqlField.SqlType + "]", sqlField.Required ? "not null" : "null");
                }
            }

            ILogger logger = ComponentService.GetInstance<ILogger>();
            logger.LogMessage("Information", "Create Table: " + String.Format(Queries.CreateContentTable, tableSchema, tableName, columnList));

            _dataContext.ExecuteCommand(String.Format(Queries.CreateContentTable, tableSchema, tableName, columnList));
        }

        public void UpdateContentTable(string tableSchema, string tableName, IEnumerable<Field> fields)
        {   
            if (!_updatedTableList.Contains(_dataContext.Connection.ConnectionString + tableName))
            {
                string updateQuery = "begin" + Environment.NewLine;
                foreach (Field field in fields)
                {
                    if (field is SqlMappedField)
                    {
                        SqlMappedField sqlField = (SqlMappedField)field;
                        updateQuery += String.Format(Queries.UpdateSingleTableColumn, tableSchema, tableName, GetColumnName(sqlField), "[" + GetColumnName(sqlField) + "] [" + (sqlField.SqlType.Contains("(") ? sqlField.SqlType.Replace("(", "](") : sqlField.SqlType + "]") + " " + (sqlField.Required ? "not null" : "null")) + Environment.NewLine + Environment.NewLine;
                    }
                }

                updateQuery += "end";

                ILogger logger = ComponentService.GetInstance<ILogger>();
                logger.LogMessage("Information", "Update Table: " + updateQuery);

                _dataContext.ExecuteCommand(updateQuery);

                _updatedTableList.Add(_dataContext.Connection.ConnectionString + tableName);
            }
        }

        public void CreateSchema(string schemaName)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.CreateSchema, schemaName));
        }

        public void EnsureDatabaseExists()
        {
            if (!_databaseCreated && !_dataContext.DatabaseExists())
            {
                throw new DataException("Database does not exist.");
                //_dataContext.CreateDatabase();
            }

            _databaseCreated = true;
        }

        public void AddAssociationTableSynchronizationObjects(string tableSchema, string tableName, string firstTypeName, string secondTypeName)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.AddIsSynchedColumn, tableSchema, tableName));
            if (_dataContext.ExecuteQuery<int>(String.Format(Queries.VerifySynchUpdateTriggerExists, tableSchema, tableName)).FirstOrDefault() == 0)
            {
                if (firstTypeName == secondTypeName)
                {
                    _dataContext.ExecuteCommand(String.Format(Queries.AddSynchUpdateAssociationTrigger, tableSchema, tableName, firstTypeName, secondTypeName + "2"));
                }
                else
                {
                    _dataContext.ExecuteCommand(String.Format(Queries.AddSynchUpdateAssociationTrigger, tableSchema, tableName, firstTypeName, secondTypeName));
                }
            }
        }

        public void AddTableSynchronizationObjects(string tableSchema, string tableName)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.AddIsSynchedColumn, tableSchema, tableName));
            if (_dataContext.ExecuteQuery<int>(String.Format(Queries.VerifySynchUpdateTriggerExists, tableSchema, tableName)).FirstOrDefault() == 0)
            {
                _dataContext.ExecuteCommand(String.Format(Queries.AddSynchUpdateTrigger, tableSchema, tableName));
            }
        }

        public int DropTableSynchronizationObjects(string tableSchema, string tableName, bool deleteItems)
        {
            int deletedItems = 0;
            if (deleteItems)
            {
                deletedItems = _dataContext.ExecuteCommand(String.Format(Queries.ClearNonSynchedData, tableSchema, tableName));
            }
            _dataContext.ExecuteCommand(String.Format(Queries.RemoveSynchUpdateTrigger, tableSchema, tableName));
            _dataContext.ExecuteCommand(String.Format(Queries.RemoveIsSynchedColumn, tableSchema, tableName));
            return deletedItems;
        }

        public bool VerifySchemaExists(string schemaName)
        {
            EnsureDatabaseExists();
            bool exists = false;           
            exists = _dataContext.ExecuteQuery<int>(String.Format(Queries.VerifySchemaExists, schemaName)).FirstOrDefault() > 0;           
            return exists;
        }

        public bool VerifyTableExists(string tableSchema, string tableName)
        {
            EnsureDatabaseExists();
            bool exists = false;
            if (_verifiedTableList.Contains(_dataContext.Connection.ConnectionString + tableName))
            {
                exists = true;
            }
            else
            {
                exists = _dataContext.ExecuteQuery<int>(String.Format(Queries.VerifyTableExists, tableSchema, tableName)).FirstOrDefault() > 0;
                if (exists)
                {
                    _verifiedTableList.Add(_dataContext.Connection.ConnectionString + tableName);
                }
            }
            return exists;
        }

        public void ResetVerifiedTableList()
        {
            _verifiedTableList.Clear();
            _updatedTableList.Clear();
        }

        #endregion

        #region Data Management

        public void DeleteAllAssociations(string tableSchema, string tableName, string typeName, string id)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.DeleteAllAssociations, tableSchema, tableName, typeName, id));
        }

        public void DeleteAssociation(string tableSchema, string tableName, string firstTypeName, string firstId, string secondTypeName, string secondId)
        {
            if (firstTypeName == secondTypeName)
            {
                _dataContext.ExecuteCommand(String.Format(Queries.DeleteAssociation, tableSchema, tableName, firstTypeName, firstId, secondTypeName + "2", secondId));
            }
            else
            {
                _dataContext.ExecuteCommand(String.Format(Queries.DeleteAssociation, tableSchema, tableName, firstTypeName, firstId, secondTypeName, secondId));
            }
        }

        public void DeleteItem(string tableSchema, string tableName, string id)
        {
            _dataContext.ExecuteCommand(String.Format(Queries.DeleteItem, tableSchema, tableName, id));
        }

        public void InsertAssociation(string tableSchema, string tableName, string firstTypeName, string firstId, string secondTypeName, string secondId)
        {
            string columnString = "";
            string columnFormat = "[{0}],";
            int columns = 0;
            foreach (TableColumn column in RetrieveOrderedColumnNamesForTable(tableSchema, tableName))
            {
                if (columns == 0)
                {
                    columnString += String.Format(columnFormat, column.Name);
                }
                else if (columns == 1 && column.Name == (firstTypeName + "ListItemID"))
                {
                    columnString = String.Format(columnFormat, column.Name) + columnString;
                }
                else if (columns == 1)
                {
                    columnString += String.Format(columnFormat, column.Name);
                }
                columns++;
            }
            columnString = columnString.TrimEnd(',');
            _dataContext.ExecuteCommand(String.Format(Queries.InsertAssociation, tableSchema, tableName, columnString, firstId, secondId));
        }

        public void InsertItem(string tableSchema, string tableName, Hashtable values)
        {
            string columnString = "";
            string valueString = "";
            string columnFormat = "[{0}],";
            foreach (TableColumn column in RetrieveOrderedColumnNamesForTable(tableSchema, tableName))
            {
                string format = "{0},";
                if (values.ContainsKey(column.Name))
                {
                    columnString += String.Format(columnFormat, column.Name);

                    string columnValue = values[column.Name].ToString();

                    /* JADE */

                    if (column.DataType.Contains("sql_variant"))
                    {
                        string columnValueType = columnValue.Split(";".ToCharArray())[0];

                        columnValue = columnValue.Split(";".ToCharArray())[1].Remove(0, 1);

                        switch (columnValueType)
                        {
                            case "string": column.DataType = "text"; break;
                            case "datetime": column.DataType = "date"; break;
                            case "boolean": column.DataType = "bit"; columnValue = columnValue.Equals("1") ? "true" : "false"; break;
                        }
                    }

                    /* JADE */
                    
                    if (column.DataType.Contains("char") || column.DataType.Contains("text"))
                    {
                        format = "'{0}',";
                        columnValue = columnValue.Replace("'", "''");
                    }
                    else if (String.IsNullOrEmpty(columnValue))
                    {
                        columnValue = "null";
                    }
                    else if (column.DataType.Contains("date"))
                    {
                        format = "convert(datetime, '{0}', 101),";
                        DateTime localizedDate = DateTime.Parse(columnValue);
                        columnValue = localizedDate.ToString("MM/dd/yyyy HH:mm:ss");
                    }
                    else if (column.DataType == "bit")
                    {
                        columnValue = Boolean.Parse(columnValue) ? "1" : "0";
                    }

                    valueString += String.Format(format, columnValue);
                }
            }
            valueString = valueString.TrimEnd(',');
            columnString = columnString.TrimEnd(',');

            ILogger logger = ComponentService.GetInstance<ILogger>();
            logger.LogMessage("Information", "Insert Item: " + String.Format(Queries.InsertItem, tableSchema, tableName, columnString, valueString));

            _dataContext.ExecuteCommand(String.Format(Queries.InsertItem, tableSchema, tableName, columnString, valueString));
        }

        public bool UpdateItem(string tableSchema, string tableName, string id, Hashtable values)
        {
            string valueString = "";
            foreach (TableColumn column in RetrieveOrderedColumnNamesForTable(tableSchema, tableName))
            {
                string format = "[{0}] = {1},";
                if (values.ContainsKey(column.Name))
                {   
                    string columnValue = values[column.Name].ToString();

                    /* JADE */

                    if (column.DataType.Contains("sql_variant"))
                    {
                        string columnValueType = columnValue.Split(";".ToCharArray())[0];

                        columnValue = columnValue.Split(";".ToCharArray())[1].Remove(0, 1);

                        switch (columnValueType)
                        {
                            case "string": column.DataType = "text"; break;
                            case "datetime": column.DataType = "date"; break;
                            case "boolean": column.DataType = "bit"; columnValue = columnValue.Equals("1") ? "true" : "false"; break;
                        }
                    }

                    /* JADE */

                    if (column.DataType.Contains("char") || column.DataType.Contains("text"))
                    {
                        format = "[{0}] = '{1}',";
                        columnValue = columnValue.Replace("'", "''");
                    }
                    else if (String.IsNullOrEmpty(columnValue))
                    {
                        columnValue = "null";
                    }
                    else if (column.DataType.Contains("date"))
                    {
                        format = "[{0}] = convert(datetime, '{1}', 101),";
                        DateTime localizedDate = DateTime.Parse(columnValue);
                        columnValue = localizedDate.ToString("MM/dd/yyyy HH:mm:ss");
                    }
                    else if (column.DataType == "bit")
                    {
                        columnValue = Boolean.Parse(columnValue) ? "1" : "0";
                    }

                    valueString += String.Format(format, column.Name, columnValue);
                }
            }
            valueString = valueString.TrimEnd(',');

            ILogger logger = ComponentService.GetInstance<ILogger>();
            logger.LogMessage("Information", "Update Item: " + String.Format(Queries.UpdateItem, tableSchema, tableName, valueString, id));

            int rowsUpdated = _dataContext.ExecuteCommand(String.Format(Queries.UpdateItem, tableSchema, tableName, valueString, id));
            return rowsUpdated > 0;
        }

        private string GetColumnName(SqlMappedField field)
        {
            return String.IsNullOrEmpty(field.Column) ? Regex.Replace(field.Name, "[\\W\\s]", "").Replace("_x0020_", "") : field.Column;
        }

        private IEnumerable<TableColumn> RetrieveOrderedColumnNamesForTable(string tableSchema, string tableName)
        {   
            return _dataContext.ExecuteQuery<TableColumn>(String.Format(Queries.RetreiveOrderedColumnsNamesForTable, tableSchema, tableName));            
        }        

        #endregion

        #region IDisposable Members

        public void Dispose()
        {   
            _dataContext.Dispose();
        }

        #endregion
    }

    internal class TableColumn
    {
        public string Name { get; set; }
        public string DataType { get; set; }

        public TableColumn() {}
    }    
}
